﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CalculeazaStocCritic]
	@id_Medicament int,
	@numar_de_saptamani_in_statistici int,
	@stocCritic float output
AS
BEGIN
	SET DATEFIRST 1;
	DECLARE @dataCurenta datetime = GetDate()
	DECLARE @partToSubtract int = 0 - @numar_de_saptamani_in_statistici
	DECLARE @dataInceput datetime = DateAdd(wk,@partToSubtract,@dataCurenta)
	
	DECLARE @cantitate_maxima_vanduta float = 0
	DECLARE @varianta_total float = 0
	DECLARE @varianta_diff float = 0
	DECLARE @varianta float = 0
	DECLARE @numarZileLivrare int
	
	DECLARE @cantitati_maxime TABLE
	( 
		CANTITATE FLOAT,
		Saptamana INT,
		RowNumber INT
	)
	
	DECLARE @cantitati_maxime_ordonate TABLE
	(
		Cantitate FLOAT,
		RowNumber INT
	)
	
	SELECT 
		@cantitate_maxima_vanduta = max(Cantitate) 
	FROM 
		Arhiva 
	WHERE 
		Id_Medicament = @id_Medicament AND
		DATA >= @dataInceput AND
		DATA <= @dataCurenta;
	
	IF @cantitate_maxima_vanduta IS NULL
	BEGIN
		SET @cantitate_maxima_vanduta = 0
	END;
	
	WITH CTE(Cantitate,Saptamana,RowNumber) AS 
	(
		SELECT 
			MAX(Cantitate),DATEPART(WK,DATA), ROW_NUMBER() OVER (PARTITION BY DATEPART(WK,DATA) ORDER BY MAX(Cantitate) asc)
		FROM 
			Arhiva
		WHERE 
			Id_Medicament = @id_Medicament AND 
			DATA >= @dataInceput AND
			DATA <= @dataCurenta 
		GROUP BY DATEPART(WK,Data)
	)
	INSERT INTO @cantitati_maxime(CANTITATE,Saptamana,RowNumber)
	SELECT Cantitate,Saptamana, RowNumber
	FROM CTE
	WHERE RowNumber = 1;
	
	WITH CTE(Cantitate,RowNumber) AS
	(
		SELECT Cantitate,ROW_NUMBER() OVER (ORDER BY Saptamana asc)
		FROM @cantitati_maxime
	)
	INSERT INTO @cantitati_maxime_ordonate(CANTITATE,RowNumber)
	SELECT Cantitate,RowNumber f
	FROM CTE
	
	SELECT @varianta_total = SUM(Cantitate)
	FROM @cantitati_maxime_ordonate
	WHERE RowNumber > 1
	
	SELECT @varianta_diff = SUM(Cantitate)
	FROM @cantitati_maxime_ordonate cantitati, (SELECT MAX(RowNumber) as RowNumber FROM @cantitati_maxime_ordonate) maxim
	WHERE cantitati.RowNumber < maxim.RowNumber
	
	SELECT @varianta = (@varianta_total - @varianta_diff) / (COUNT(*) - 1) 
	FROM @cantitati_maxime

	SELECT @varianta = ROUND(@varianta,0)
	
	IF @varianta IS NULL
	BEGIN
		SET @varianta = 0
	END

	SELECT 
		@numarZileLivrare = p.Timp_Livrare 
	FROM 
		Medicament m 
		INNER join Producator p 
		ON m.Id_Producator = p.Id_Producator AND
		m.Id_Medicament = @id_Medicament
		
	SET @stocCritic =  @cantitate_maxima_vanduta * @numarZileLivrare + (@varianta * convert(int,(@numarZileLivrare / 7) + 1))
END